/**
* TS Client (http://www.transparent.co.nz)
* Copyright (c) 2004 Transparent Systems Limited
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the /doc/LICENSE.txt
* This is the GNU General Public License Version 2 as published by the Free Software Foundation.
* You can download this program from <a href="http://sourceforge.com/projects/ts-client">http://sourceforge.com/projects/ts-client</a>
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License Version 2 for more details.
*
* You should have received a copy of the GNU General Public License
* Version 2 along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
*
*/
/*
* Created on Nov 14, 2003
*
*/
package nz.co.transparent.client.controller;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import nz.co.transparent.client.db.ControllerException;
import nz.co.transparent.client.db.DataSourceHandler;
import nz.co.transparent.client.db.FinderException;
import nz.co.transparent.client.db.UpdaterException;
/**
* Handles generic function for a table: - query list of records - query
* specific reord - add record - update record - delete record
*
* An update method is handled in a single transaction.
*
* Warning: Methods in this class connot be chained to be handled in one single
* transaction For transactions use class GenericTransactionController
*
* @author John Zoetebier
*
*/
public class GenericController {
private static GenericController _instance;
private Logger log = Logger.getLogger("nz.co.transparent.client.db");
private DataSource dataSource = DataSourceHandler.getDataSource();
/**
* Private constructor
*
*/
private GenericController() {
}
public static GenericController getInstance() {
if (_instance != null) {
return _instance;
}
_instance = new GenericController();
return _instance;
}
/**
* Find all records of selected table with order clause
*
* @param tableName
* Name of table
* @param orderClause
* Name of column to order result list.
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAll(String tableName, String orderClause)
throws ControllerException {
return findAllWhere(tableName, orderClause, null);
}
/**
* Find all records of selected table in random order
*
* @param tableName
* Name of table
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAll(String tableName) throws ControllerException {
return findAll(tableName, null);
}
/**
* Find all records of selected table with order clause and where clause
*
* @param tableName
* Name of table
* @param orderClause
* Name of column to order result list.
* @param whereClause
* Where clause
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAllWhere(
String tableName,
String orderClause,
String whereClause)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapListHandler();
String sql = null;
try {
sql = "SELECT * FROM " + tableName;
if (whereClause != null) {
sql += " WHERE (" + whereClause + ")";
}
if (orderClause != null) {
sql += " ORDER BY " + orderClause;
}
return (List) queryRunner.query(sql, rsh);
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Find all records of selected table with order clause and searchMap
*
* @param tableName
* Name of table
* @param orderClause
* Name of column to order result list.
* @param searchMap
* <code>Map</code> with FieldName => FieldValue mapping
* @param countMap
* <code>Map</code> with keys maxRecords and maxPassed
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAllWhere(
String tableName,
String orderClause,
Map searchMap,
Map countMap)
throws ControllerException {
countMap.put("maxPassed", Boolean.FALSE);
int maxRecords =
(countMap.get("maxRecords") == null)
? 30
: ((Integer) countMap.get("maxRecords")).intValue();
List mapList = new ArrayList();
Map recordMap = null;
Connection conn = null;
Statement stmt = null;
ResultSet rst = null;
ResultSetMetaData metaData = null;
String sql = null;
String columnName = null;
int numRecords = 0;
int i;
Set keySet = null;
Iterator iterator = null;
String key = null;
try {
DataSource dataSource = DataSourceHandler.getDataSource();
conn = dataSource.getConnection();
stmt = conn.createStatement();
stmt.setMaxRows(maxRecords + 1);
sql = "select * from " + tableName;
String whereClause = "";
keySet = searchMap.keySet();
iterator = keySet.iterator();
while (iterator.hasNext()) {
key = (String) iterator.next();
if (searchMap.get(key) != null
&& !searchMap.get(key).equals("")) {
if (!whereClause.equals("")) {
whereClause += " and ";
}
whereClause += " ("
+ tableName
+ "."
+ key
+ " like '%"
+ searchMap.get(key)
+ "%')";
}
}
if (!whereClause.equals("")) {
sql += " where (";
sql += whereClause;
sql += ")";
}
if (!orderClause.equals("")) {
sql += " order by " + orderClause;
}
rst = stmt.executeQuery(sql);
metaData = rst.getMetaData();
int numColumns = metaData.getColumnCount();
while (rst.next()) {
numRecords++;
if (numRecords > maxRecords) {
countMap.put("maxPassed", Boolean.TRUE);
break;
}
// Create new recordMap
recordMap = new HashMap(numColumns);
// Iterate over columns starting at 1 !
for (i = 1; i <= numColumns; i++) {
columnName = metaData.getColumnName(i);
Object valueObject = rst.getObject(i);
// If contact details are missing column is null
// If column already present, so not override
if (!recordMap.containsKey(columnName)) {
recordMap.put(columnName, valueObject);
}
}
mapList.add(recordMap);
//break; // TEST ONLY
}
return mapList;
} catch (SQLException se) {
String message =
"SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
} finally {
try {
DbUtils.close(rst);
DbUtils.close(stmt);
DbUtils.close(conn);
} catch (SQLException se) {
String message =
"SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
}
}
}
/**
* Find a single record in table with order and where clause
*
* @param tableName
* Table name
* @param whereClause
* Where clause.
* @return Map with {ColumnName, ColumnValue}
* @throws ControllerException
* Any exception is re-thrown as a ControllerException
* @throws FinderException
* If no record found throw FinderException
*/
public Map findWhere(String tableName, String whereClause)
throws ControllerException, FinderException {
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapHandler();
String sql = null;
try {
sql = "SELECT * FROM " + tableName;
if (whereClause != null) {
sql += " WHERE " + whereClause;
}
Map map = (Map) queryRunner.query(sql, rsh);
if (map == null) {
throw new FinderException();
}
return map;
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Check if record exists
*
* @param tableName
* Table name
* @param whereClause
* Where clause.
* @return boolean
* @throws ControllerException
* Any exception is re-thrown as a ControllerException
*/
public boolean existsRecord(
String tableName,
String whereClause,
Object[] params)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapHandler();
String sql = null;
try {
sql = "SELECT * FROM " + tableName;
if (whereClause != null) {
sql += " WHERE (" + whereClause + ")";
}
Map map = null;
if (params == null) {
map = (Map) queryRunner.query(sql, rsh);
} else {
map = (Map) queryRunner.query(sql, params, rsh);
}
if (map == null) {
return false;
} else {
return true;
}
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Check if record exists
*
* @param tableName
* Table name
* @param whereClause
* Where clause.
* @param param
* Parameter in whereClause indicated by "?"
* @return boolean
* @throws ControllerException
* Any exception is re-thrown as a ControllerException
*/
public boolean existsRecord(
String tableName,
String whereClause,
Object param)
throws ControllerException {
Object[] params = { param };
return existsRecord(tableName, whereClause, params);
}
/**
* Check if record exists
*
* @param tableName
* Table name
* @param whereClause
* Where clause.
* @return boolean
* @throws ControllerException
* Any exception is re-thrown as a ControllerException
*/
public boolean existsRecord(String tableName, String whereClause)
throws ControllerException {
return existsRecord(tableName, whereClause, null);
}
/**
* Add record to table.
*
* @param columnMap
* @param tableName
* @param primaryKeyName
* Primary key column name. Pass null value in columnMap to have
* primary key generated. A non-null value in columnMap for
* primary key is used as is.
* @return Number of records affected
* @throws ControllerException
*/
public int insertRecord(
String tableName,
String primaryKeyName,
Map columnMap)
throws ControllerException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = null;
String columnName = null;
int i;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false); // start transaction
// Create primary key in case primary key is null
if (columnMap.get(primaryKeyName) == null) {
int uniqueKey = nz.co.transparent.client.db.SQL.getUniqueKey(tableName, primaryKeyName);
columnMap.put(primaryKeyName, new Integer(uniqueKey));
}
sql = "insert into " + tableName;
String parm = null;
Set columnSet = columnMap.keySet();
Iterator iterator = columnSet.iterator();
List paramList = new ArrayList(columnSet.size());
i = 0;
int j = 0;
while (iterator.hasNext()) {
columnName = (String) iterator.next();
// CURRENT_TIMESTAMP must be set directly into SQL to force
// date created by server
// Alternatively these columns can be left out of the map
if (columnName.equals("date_created")) {
parm = "CURRENT_TIMESTAMP";
} else if (columnName.equals("date_updated")) {
parm = "CURRENT_TIMESTAMP";
} else {
parm = "?";
paramList.add(columnMap.get(columnName));
}
if (j++ == 0) {
sql += " set " + columnName + "=" + parm;
} else {
sql += " ," + columnName + "=" + parm;
}
}
try {
i = queryRunner.update(conn, sql, paramList.toArray());
conn.commit();
return i;
} catch (SQLException se) {
conn.rollback();
throw new ControllerException(se);
}
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
} finally {
try {
DbUtils.close(conn);
} catch (SQLException se) {
log.warning(
"GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
}
/**
* Delete a record
*
* @param tableName
* Name of table
* @param whereClause
* Where clause
* @return Number of records affected
* @throws ControllerException
*/
public int deleteRecord(String tableName, String whereClause)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = null;
try {
sql = "delete from " + tableName;
if (whereClause != null) {
sql += " where " + whereClause;
}
return queryRunner.update(sql);
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Delete a record
*
* @param tableName
* Name of table
* @return Number of records affected
* @throws ControllerException
*/
public int deleteRecord(String tableName) throws ControllerException {
return deleteRecord(tableName, null);
}
/**
* Update a record
*
* @param tableName
* Name of table
* @param primaryKeyName
* Name of primary key
* @param columnMap
* Map with {ColumnName, ColumnValue}
* @return Number of records affected
* @throws ControllerException
* @throws UpdaterException
* Thrown if concurrent change has happened
*/
public int updateRecord(
String tableName,
String primaryKeyName,
Map columnMap)
throws ControllerException, UpdaterException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapHandler();
String sql = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false); // Start transaction
sql = "select * from " + tableName;
sql += " where (" + primaryKeyName + "=?)";
Map columnMapTemp =
(Map) queryRunner.query(
sql,
columnMap.get(primaryKeyName),
rsh);
if (columnMapTemp == null) {
conn.rollback();
throw new ControllerException("GenericController: Cannot find record.");
}
Date oldDate = (java.util.Date) columnMap.get("date_updated");
Date newDate = (java.util.Date) columnMapTemp.get("date_updated");
if (!oldDate.equals(newDate)) {
conn.rollback();
throw new UpdaterException();
// Signal that record has already been changed
}
sql = "update " + tableName;
String parameter = null;
String columnName = null;
List columnNameList = null;
List paramList = new ArrayList();
// Iterate over columns
Set columnSet = columnMapTemp.keySet();
Iterator iterator = columnSet.iterator();
int i = 0;
while (iterator.hasNext()) {
columnName = (String) iterator.next();
if (columnName.equals("date_updated")) {
parameter = "CURRENT_TIMESTAMP";
} else {
parameter = "?";
paramList.add(columnMap.get(columnName));
}
if (i++ == 0) {
sql += " set " + columnName + " = " + parameter;
} else {
sql += " ," + columnName + " = " + parameter;
}
}
sql += " where (" + primaryKeyName + "=?)";
paramList.add(columnMap.get(primaryKeyName));
// Add primaryKey value to paramList
int result = queryRunner.update(sql, paramList.toArray());
if (result == 0) {
conn.rollback();
} else {
conn.commit();
}
return result;
} catch (SQLException se) {
log.warning("SQL Exception: " + se.getMessage());
try {
conn.rollback();
} catch (SQLException se2) {
log.warning("SQL Exception: " + se2.getMessage());
throw new ControllerException(se2);
}
throw new ControllerException(se);
} finally {
try {
DbUtils.close(conn);
} catch (SQLException se3) {
log.warning("SQL Exception: " + se3.getMessage());
throw new ControllerException(se3);
}
}
}
/**
* @param displayColumnName
* Name of column displayed in combobox with list
* @param displayColumnValue
* Value displayed
* @param tableName
* Table name
* @param primaryKeyName
* Name of primary key
* @param list
* List
* @return Integer with foreign key
* @throws ControllerException
*/
public Integer getForeignKey(
String displayColumnName,
String displayColumnValue,
String tableName,
String primaryKeyName,
List list)
throws ControllerException {
Map columnMap;
Iterator iterator = list.iterator();
Integer keyID = null;
while (iterator.hasNext()) {
columnMap = (Map) iterator.next();
if (displayColumnValue.equals(columnMap.get(displayColumnName))) {
keyID = (Integer) columnMap.get(primaryKeyName);
return keyID;
}
}
// Add new record
QueryRunner queryRunner = new QueryRunner(dataSource);
columnMap = new HashMap();
columnMap.put(primaryKeyName, null); // Have key
// generated
columnMap.put(displayColumnName, displayColumnValue);
//columnMap.put("date_created", null);
//columnMap.put("date_updated", null);
columnMap.put(
"updater_person_id",
LoginController.getPerson().get("person_id"));
insertRecord(tableName, primaryKeyName, columnMap);
// Will insert new key
list.add(columnMap);
return (Integer) columnMap.get(primaryKeyName);
}
public void switchOffOtherDefault(
String tableName,
String primaryKeyName,
Integer primaryKeyValue,
boolean isDefault)
throws ControllerException {
// If this record is no default, return
if (!isDefault) {
return;
}
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapHandler();
String sql = null;
sql = "select * from " + tableName;
sql += " where (";
sql += " (is_default=true)";
if (primaryKeyValue != null) {
sql += " and ("
+ primaryKeyName
+ "!="
+ primaryKeyValue.intValue()
+ ")";
}
sql += ")";
try {
Map recordMap = (Map) queryRunner.query(sql, rsh);
if (recordMap == null) {
return;
}
recordMap.put("is_default", Boolean.FALSE);
updateRecord(tableName, primaryKeyName, recordMap);
} catch (SQLException se) {
log.warning("GenericController: " + se.getMessage());
throw new ControllerException(se);
} catch (UpdaterException ue) {
throw new ControllerException(ue);
}
}
/**
* Grant permission on a database object to user
*
* @param permission
* @param databaseObject
* @param userName
* @param withGrantOption
* @throws ControllerException
*/
public void grantPermission(
String permission,
String databaseObject,
String userName,
boolean withGrantOption)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql =
"grant " + permission + " on " + databaseObject + " to " + userName;
if (withGrantOption) {
sql += " with grant option";
}
try {
queryRunner.update(sql);
} catch (SQLException se) {
log.warning("GenericController: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Revoke permission on a database object from user
*
* @param permission
* @param databaseObject
* @param userName
* @throws ControllerException
*/
public void revokePermission(
String permission,
String databaseObject,
String userName)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql =
"revoke "
+ permission
+ " on "
+ databaseObject
+ " from "
+ userName;
try {
queryRunner.update(sql);
} catch (SQLException se) {
log.warning("GenericController: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Removes all permission from all database objects for selected userName
* This includes grant option permission
*
* @param userName UserName used to logon to system
* @throws ControllerException
*/
public void revokeAllPermissions(String userName)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql =
"delete from SYS_INFO.sUSRGrant where (grantee='" + userName + "')";
try {
queryRunner.update(sql);
} catch (SQLException se) {
log.warning("GenericController: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Revoke grant option for selected permission on database object from user
*
* @param permission
* @param databaseObject
* @param userName
* @throws ControllerException
*/
public void revokeGrantOption(
String permission,
String databaseObject,
String userName)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql =
"revoke grant option for "
+ permission
+ " on "
+ databaseObject
+ " from "
+ userName;
try {
queryRunner.update(sql);
} catch (SQLException se) {
log.warning("GenericController: " + se.getMessage());
throw new ControllerException(se);
}
}
}